/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Exam;
import java.sql.*;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import util.DBUtil;

/**
 *
 * @author NHAN
 */
public class ExamDAO {

    Connection conn;
    private int totalRecords = 0;
    String sqlSelectByID = "select * from Exam where id=?";
    String sqlCreate = "insert into Exam([name],time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state) values(?,?, ?, ?, ?,?,?,?,?)";
    String sqlUpdate = "update Exam set [name]=?,time=?, start_time=?, end_time=?, class_id=?,created_date=?,created_by=?,last_modified_date=?,state=? where id=?";
    String sqlDelete = "delete from Exam where id=?";
    Logger logger = Logger.getLogger(ExamDAO.class);

    public ExamDAO() {
    }

    /**
     * get a Exam by specifying identifier value
     *
     * @param id
     * @return Exam object
     */
    public Exam getExam(int id) {
        Exam e = new Exam();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state
            PreparedStatement ps = conn.prepareStatement(sqlSelectByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                e.setId(id);
                e.setName(rs.getString("name"));
                e.setTime(rs.getInt("time"));
                e.setStartTime(rs.getDate("start_time"));
                e.setEndTime(rs.getDate("end_time"));
                e.getCls().setId(rs.getInt("class_id"));
                e.setCreatedDate(rs.getDate("created_date"));
                e.getAuthor().setFirstName(rs.getString("created_by"));
                e.setModifiedDate(rs.getDate("last_modified_date"));
                e.setState(rs.getBoolean("state"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return e;
    }

    /**
     *
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of Exams
     */
    public ArrayList<Exam> getExams(int currentPage, int pageSize) {
        ArrayList<Exam> list = new ArrayList<Exam>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Exam", "id", "id");
            qo.setColumns("id,[name] ,time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            //time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Exam e = new Exam();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setTime(rs.getInt("time"));
                e.setStartTime(rs.getDate("start_time"));
                e.setEndTime(rs.getDate("end_time"));
                e.getCls().setId(rs.getInt("class_id"));
                e.setCreatedDate(rs.getDate("created_date"));
                e.getAuthor().setFirstName(rs.getString("created_by"));
                e.setModifiedDate(rs.getDate("last_modified_date"));
                e.setState(rs.getBoolean("state"));
                list.add(e);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<Exam> getExams(int currentPage, int pageSize, int classId) {
        ArrayList<Exam> list = new ArrayList<Exam>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Exam", "id", "id");
            qo.setColumns("id,[name] ,time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state");
            qo.setWhereClause("class_id=" + classId);
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Exam e = new Exam();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setTime(rs.getInt("time"));
                e.setStartTime(rs.getDate("start_time"));
                e.setEndTime(rs.getDate("end_time"));
                e.getCls().setId(rs.getInt("class_id"));
                e.setCreatedDate(rs.getDate("created_date"));
                e.getAuthor().setFirstName(rs.getString("created_by"));
                e.setModifiedDate(rs.getDate("last_modified_date"));
                e.setState(rs.getBoolean("state"));
                list.add(e);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<Exam> getExamsOfUser(int currentPage, int pageSize, int userId) {
        ArrayList<Exam> list = new ArrayList<Exam>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Exam", "id", "id");
            qo.setColumns("id,[name] ,time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state");
            qo.setWhereClause("created_by=" + userId);
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            //time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Exam e = new Exam();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setTime(rs.getInt("time"));
                e.setStartTime(rs.getDate("start_time"));
                e.setEndTime(rs.getDate("end_time"));
                e.getCls().setId(rs.getInt("class_id"));
                e.setCreatedDate(rs.getDate("created_date"));
                e.getAuthor().setFirstName(rs.getString("created_by"));
                e.setModifiedDate(rs.getDate("last_modified_date"));
                e.setState(rs.getBoolean("state"));
                list.add(e);
            }
            totalRecords = qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    /**
     * add new Exam
     *
     * @param e Exam
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(Exam e) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }//time,start_time,end_time,class_id,created_date,created_by,last_modified_date,state
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setString(1, e.getName());
            ps.setInt(2, e.getTime());
            ps.setDate(3, new java.sql.Date(e.getStartTime().getTime()));
            ps.setDate(4, new java.sql.Date(e.getEndTime().getTime()));
            ps.setInt(5, e.getCls().getId());
            ps.setDate(6, new java.sql.Date(e.getCreatedDate().getTime()));
            ps.setInt(7, e.getAuthor().getId());
            ps.setDate(8, new java.sql.Date(e.getModifiedDate().getTime()));
            ps.setBoolean(9, e.isState());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update specified Exam
     *
     * @param e Exam to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(Exam e) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, e.getName());
            ps.setInt(2, e.getTime());
            ps.setDate(3, new java.sql.Date(e.getStartTime().getTime()));
            ps.setDate(4, new java.sql.Date(e.getEndTime().getTime()));
            ps.setInt(5, e.getCls().getId());
            ps.setDate(6, new java.sql.Date(e.getCreatedDate().getTime()));
            ps.setString(7, e.getAuthor().getFirstName());
            ps.setDate(8, new java.sql.Date(e.getModifiedDate().getTime()));
            ps.setBoolean(9, e.isState());
            ps.setInt(10, e.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }

    /**
     * delete a Exam by specifying identifier value
     *
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }

    /**
     * @return the totalRecords
     */
    public int getTotalRecords() {
        return totalRecords;
    }

    /**
     * @param totalRecords the totalRecords to set
     */
    public void setTotalRecords(int totalRecords) {
        this.totalRecords = totalRecords;
    }
}
